﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace UI.Lib
{
    public class ExportHelper
    {
        private string mFilePath = "";
        private DataSet mDs = new DataSet();


        public ExportHelper(string path, ref DataSet ds)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.mDs = ds;
            this.mFilePath = path;
        }

        /// <summary>
        /// 将工作簿中指定的sheet转换成dataset中的表
        /// </summary>
        /// <param name="pos">sheet在工作簿中的位置</param>
        /// <returns>成功返回true</returns>
        public bool Convert(int pos)
        {
            bool r = false;
            string strSql = "";
            string sheetName = "";
            System.Data.DataTable tTable;
            OleDbDataAdapter objDa;
            //
            //创建excel进程
            //
            object obj = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass xxApp = new Microsoft.Office.Interop.Excel.ApplicationClass();//.Application(); 
            Microsoft.Office.Interop.Excel.Workbook xxBook = null;
            Microsoft.Office.Interop.Excel._Worksheet xxSheet = null;

            try
            {

                //
                //打开excel文件，并获取指定sheet的名字
                //
                xxBook = xxApp.Workbooks.Open(this.mFilePath, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);//添加新工作簿
                xxSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xxBook.Worksheets[pos]);
                sheetName = xxSheet.Name.ToString();
                //
                //释放excel资源
                //
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xxSheet);
                xxSheet = null;
                GC.Collect();
                xxBook.Close(false, obj, obj);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xxBook);
                xxBook = null;
                xxApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xxApp);
                xxApp = null;
                //
                //创建数据连接
                //
                OleDbConnection objConn = new OleDbConnection(
                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.mFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");

                //
                //获取工作簿中的表
                //
                strSql = "select * from [" + sheetName + "$]";
                tTable = new System.Data.DataTable(sheetName);
                //
                //将sheet填入table中
                //
                objDa = new OleDbDataAdapter(strSql, objConn);
                objDa.Fill(tTable);
                this.mDs.Tables.Add(tTable);
                //
                //摧毁连接
                //
                objConn.Dispose();
                r = true;

            }
            catch
            {
                r = false;
            }

            GC.Collect();
            return r;
        }

    }
}
